<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">
	<title>Grouping Functions | ElasticSearch 7.7 权威指南中文版</title>
	<meta name="keywords" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <meta name="description" content="ElasticSearch 权威指南中文版, elasticsearch 7, es7, 实时数据分析，实时数据检索" />
    <!-- Give IE8 a fighting chance -->
    <!--[if lt IE 9]>
    <script src="https://oss.maxcdn.com/html5shiv/3.7.2/html5shiv.min.js"></script>
    <script src="https://oss.maxcdn.com/respond/1.4.2/respond.min.js"></script>
    <![endif]-->
	<link rel="stylesheet" type="text/css" href="../static/styles.css" />
	<script>
	var _link = 'sql-functions-grouping.html';
    </script>
</head>
<body>
<div class="main-container">
    <section id="content">
        <div class="content-wrapper">
            <section id="guide" lang="zh_cn">
                <div class="container">
                    <div class="row">
                        <div class="col-xs-12 col-sm-8 col-md-8 guide-section">
                            <div style="color:gray; word-break: break-all; font-size:12px;">原英文版地址: <a href="https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-functions-grouping.html" rel="nofollow" target="_blank">https://www.elastic.co/guide/en/elasticsearch/reference/7.7/sql-functions-grouping.html</a>, 原文档版权归 www.elastic.co 所有<br/>本地英文版地址: <a href="../en/sql-functions-grouping.html" rel="nofollow" target="_blank">../en/sql-functions-grouping.html</a></div>
                        <!-- start body -->
                  <div class="page_header">
<strong>重要</strong>: 此版本不会发布额外的bug修复或文档更新。最新信息请参考 <a href="https://www.elastic.co/guide/en/elasticsearch/reference/current/index.html" rel="nofollow">当前版本文档</a>。
</div>
<div id="content">
<div class="breadcrumbs">
<span class="breadcrumb-link"><a href="index.html">Elasticsearch Guide [7.7]</a></span>
»
<span class="breadcrumb-link"><a href="xpack-sql.html">SQL access</a></span>
»
<span class="breadcrumb-link"><a href="sql-functions.html">Functions and Operators</a></span>
»
<span class="breadcrumb-node">Grouping Functions</span>
</div>
<div class="navheader">
<span class="prev">
<a href="sql-functions-aggs.html">« Aggregate Functions</a>
</span>
<span class="next">
<a href="sql-functions-datetime.html">Date/Time and Interval Functions and Operators »</a>
</span>
</div>
<div class="section xpack">
<div class="titlepage"><div><div>
<h2 class="title">
<a id="sql-functions-grouping"></a>Grouping Functions<a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/grouping.asciidoc">edit</a><a class="xpack_tag" href="https://www.elastic.co/subscriptions"></a>
</h2>
</div></div></div>
<p>Functions for creating special <em>grouping</em>s (also known as <em>bucketing</em>); as such these need to be used
as part of the <a class="xref" href="sql-syntax-select.html#sql-syntax-group-by" title="GROUP BY">grouping</a>.</p>
<div class="section">
<div class="titlepage"><div><div>
<h3 class="title">
<a id="sql-functions-grouping-histogram"></a><code class="literal">HISTOGRAM</code><a class="edit_me edit_me_private" rel="nofollow" title="Editing on GitHub is available to Elastic" href="https://github.com/elastic/elasticsearch/edit/7.7/docs/reference/sql/functions/grouping.asciidoc">edit</a>
</h3>
</div></div></div>
<p><strong>Synopsis:</strong></p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">HISTOGRAM(
    numeric_exp,        <a id="CO91-1"></a><i class="conum" data-value="1"></i>
    numeric_interval)   <a id="CO91-2"></a><i class="conum" data-value="2"></i>

HISTOGRAM(
    date_exp,           <a id="CO91-3"></a><i class="conum" data-value="3"></i>
    date_time_interval) <a id="CO91-4"></a><i class="conum" data-value="4"></i></pre>
</div>
<p><span class="strong strong"><strong>Input</strong></span>:</p>
<div class="calloutlist">
<table border="0" summary="Callout list">
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO91-1"><i class="conum" data-value="1"></i></a></p>
</td>
<td align="left" valign="top">
<p>numeric expression (typically a field)</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO91-2"><i class="conum" data-value="2"></i></a></p>
</td>
<td align="left" valign="top">
<p>numeric interval</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO91-3"><i class="conum" data-value="3"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/time expression (typically a field)</p>
</td>
</tr>
<tr>
<td align="left" valign="top" width="5%">
<p><a href="#CO91-4"><i class="conum" data-value="4"></i></a></p>
</td>
<td align="left" valign="top">
<p>date/time <a class="xref" href="sql-functions-datetime.html#sql-functions-datetime-interval" title="Intervals">interval</a></p>
</td>
</tr>
</table>
</div>
<p><span class="strong strong"><strong>Output</strong></span>: non-empty buckets or groups of the given expression divided according to the given interval</p>
<p><span class="strong strong"><strong>Description</strong></span>: The histogram function takes all matching values and divides them into buckets with fixed size matching the given interval, using (roughly) the following formula:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">bucket_key = Math.floor(value / interval) * interval</pre>
</div>
<div class="note admon">
<div class="icon"></div>
<div class="admon_content">
<p>The histogram in SQL does <span class="strong strong"><strong>NOT</strong></span> return empty buckets for missing intervals as the traditional <a class="xref" href="search-aggregations-bucket-histogram-aggregation.html" title="Histogram Aggregation">histogram</a> and  <a class="xref" href="search-aggregations-bucket-datehistogram-aggregation.html" title="Date histogram aggregation">date histogram</a>. Such behavior does not fit conceptually in SQL which treats all missing values as <code class="literal">NULL</code>; as such the histogram places all missing values in the <code class="literal">NULL</code> group.</p>
</div>
</div>
<p><code class="literal">Histogram</code> can be applied on either numeric fields:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT HISTOGRAM(salary, 5000) AS h FROM emp GROUP BY h;

       h
---------------
25000
30000
35000
40000
45000
50000
55000
60000
65000
70000</pre>
</div>
<p>or date/time fields:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT HISTOGRAM(birth_date, INTERVAL 1 YEAR) AS h, COUNT(*) AS c FROM emp GROUP BY h;


           h            |       c
------------------------+---------------
null                    |10
1952-01-01T00:00:00.000Z|8
1953-01-01T00:00:00.000Z|11
1954-01-01T00:00:00.000Z|8
1955-01-01T00:00:00.000Z|4
1956-01-01T00:00:00.000Z|5
1957-01-01T00:00:00.000Z|4
1958-01-01T00:00:00.000Z|7
1959-01-01T00:00:00.000Z|9
1960-01-01T00:00:00.000Z|8
1961-01-01T00:00:00.000Z|8
1962-01-01T00:00:00.000Z|6
1963-01-01T00:00:00.000Z|7
1964-01-01T00:00:00.000Z|4
1965-01-01T00:00:00.000Z|1</pre>
</div>
<p>Expressions inside the histogram are also supported as long as the
return type is numeric:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT HISTOGRAM(salary % 100, 10) AS h, COUNT(*) AS c FROM emp GROUP BY h;

       h       |       c
---------------+---------------
0              |10
10             |15
20             |10
30             |14
40             |9
50             |9
60             |8
70             |13
80             |3
90             |9</pre>
</div>
<p>Do note that histograms (and grouping functions in general) allow custom expressions but cannot have any functions applied to them in the <code class="literal">GROUP BY</code>. In other words, the following statement is <span class="strong strong"><strong>NOT</strong></span> allowed:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT MONTH(HISTOGRAM(birth_date), 2)) AS h, COUNT(*) as c FROM emp GROUP BY h ORDER BY h DESC;</pre>
</div>
<p>as it requires two groupings (one for histogram followed by a second for applying the function on top of the histogram groups).</p>
<p>Instead one can rewrite the query to move the expression on the histogram <em>inside</em> of it:</p>
<div class="pre_wrapper lang-sql">
<pre class="programlisting prettyprint lang-sql">SELECT HISTOGRAM(MONTH(birth_date), 2) AS h, COUNT(*) as c FROM emp GROUP BY h ORDER BY h DESC;

       h       |       c
---------------+---------------
12             |7
10             |17
8              |16
6              |16
4              |18
2              |10
0              |6
null           |10</pre>
</div>
<div class="important admon">
<div class="icon"></div>
<div class="admon_content">
<p>When the histogram in SQL is applied on <span class="strong strong"><strong>DATE</strong></span> type instead of <span class="strong strong"><strong>DATETIME</strong></span>, the interval specified is truncated to
the multiple of a day. E.g.: for <code class="literal">HISTOGRAM(CAST(birth_date AS DATE), INTERVAL '2 3:04' DAY TO MINUTE)</code> the interval
actually used will be <code class="literal">INTERVAL '2' DAY</code>. If the interval specified is less than 1 day, e.g.:
<code class="literal">HISTOGRAM(CAST(birth_date AS DATE), INTERVAL '20' HOUR)</code> then the interval used will be <code class="literal">INTERVAL '1' DAY</code>.</p>
</div>
</div>
<div class="important admon">
<div class="icon"></div>
<div class="admon_content">
<p>All intervals specified for a date/time HISTOGRAM will use a <a class="xref" href="search-aggregations-bucket-datehistogram-aggregation.html" title="Date histogram aggregation">fixed interval</a>
in their <code class="literal">date_histogram</code> aggregation definition, with the notable exceptions of <code class="literal">INTERVAL '1' YEAR</code>, <code class="literal">INTERVAL '1' MONTH</code> and <code class="literal">INTERVAL '1' DAY</code>  where a calendar interval is used.
The choice for a calendar interval was made for having a more intuitive result for YEAR, MONTH and DAY groupings. In the case of YEAR, for example, the calendar intervals consider a one year
bucket as the one starting on January 1st that specific year, whereas a fixed interval one-year-bucket considers one year as a number
of milliseconds (for example, <code class="literal">31536000000ms</code> corresponding to 365 days, 24 hours per day, 60 minutes per hour etc.). With fixed intervals,
the day of February 5th, 2019 for example, belongs to a bucket that starts on December 20th, 2018 and Elasticsearch (and implicitly Elasticsearch SQL) would
have returned the year 2018 for a date that’s actually in 2019. With calendar interval this behavior is more intuitive, having the day of
February 5th, 2019 actually belonging to the 2019 year bucket.</p>
</div>
</div>
<div class="important admon">
<div class="icon"></div>
<div class="admon_content">
<p>Histogram in SQL cannot be applied applied on <span class="strong strong"><strong>TIME</strong></span> type.
E.g.: <code class="literal">HISTOGRAM(CAST(birth_date AS TIME), INTERVAL '10' MINUTES)</code> is currently not supported.</p>
</div>
</div>
</div>

</div>
<div class="navfooter">
<span class="prev">
<a href="sql-functions-aggs.html">« Aggregate Functions</a>
</span>
<span class="next">
<a href="sql-functions-datetime.html">Date/Time and Interval Functions and Operators »</a>
</span>
</div>
</div>

                  <!-- end body -->
                        </div>
                        <div class="col-xs-12 col-sm-4 col-md-4" id="right_col">
                        
                        </div>
                    </div>
                </div>
            </section>
        </div>
    </section>
</div>
<script src="../static/cn.js"></script>
</body>
</html>